<?php
namespace Report\Controller;
use Think\Controller;
class ReportController extends Controller {
    public function history(){//关闭交易
        $this->display();
    }

    public function uploadCsv(){//上传CSV
        $login=[];
        $upload = new \Think\Upload();// 实例化上传类
        $upload->maxSize=3145728 ;// 设置附件上传大小
        $upload->exts=array('csv', 'CSV');// 设置附件上传类型
        $upload->rootPath='./Uploads/'; // 设置附件上传根目录
        $upload->savePath='csv/'; // 设置附件上传（子）目录
        //$upload->saveName='';
        // 上传文件
        $info=$upload->upload();
        if (!$info){
            //捕获上传异常
            $this->ajaxReturn(array('msg'=>$upload->getError(),'status'=>0));
        } else {
            //取得成功上传的文件信息
            $path='Uploads/'.$info['csv']['savepath'].$info['csv']['savename'];
            $fp = fopen($path,'r') or die(json_encode(array('msg'=>'can\'t open file !!!','status'=>0)));
            while($csv_line = fgetcsv($fp)){
                $login[]=htmlentities($csv_line[0]);
            }
            fclose($fp);
            $login_str=implode(',',$login);
            $this->ajaxReturn(array('msg'=>$login_str,'status'=>1));
        }
    }

    public function downloadHistory(){//下载交易历史
        $from=I('post.from') ? I('post.from') : die('请选择开始时间，<a href="javascript:history.back(-1)">点击返回</a>');
        $to=I('post.to') ? I('post.to') : die('请选择结束时间，<a href="javascript:history.back(-1)">点击返回</a>');
        $transaction_type=I('post.transaction_type') ? I('post.transaction_type') : 0;
        $type=I('post.type') ? I('post.type') : die('请选择按账号查询或组别查询，<a href="javascript:history.back(-1)">点击返回</a>');
        $userModel=M('user');
        $use_where='(user_id>1000) ';
        if($type==1){
            $logins=I('post.keywords') ? I('post.keywords') : '%';
            if($logins!='%'){
                $use_where.=' and (user_id in ('.$logins.'))';
            }
        }else{
            $groups=I('post.keywords') ? I('post.keywords') : '%';
            if($groups!='%'){
                $groups=str_replace(",","','",$groups);//组别是字符串，所以需要加引号
                $use_where.=' and (`group` in (\''.$groups.'\'))';
            }
        }
        /*try{//放到自己数据库查询筛选一次，避免查询到其他白标账号
            $users=$userModel->field('user_id')->where($use_where)->select();
        }catch(\think\Exception $e) {
            //echo $e->getMessage();
            exit('您要查询的用户数量过多，请分批导入查询，<a href="javascript:history.back(-1)">点击返回</a>');
        }*/
        $users=$userModel->field('user_id')->where($use_where)->select();
        $login=[];
        foreach ($users as $ke => $va){
            $login[]=$va['user_id'];
        }
        //查询交易
        $model=M('mt4_trades','','DB_CONFIG2');
        if(empty($transaction_type)){//交易查询条件
            $where='(CMD between 0 and 1) and (LOGIN in ('.implode(',',$login).')) and (CLOSE_TIME between \''.$from.'\' and \''.$to.'\')';
        }else{
            if($transaction_type==1){
                $where='(CMD between 0 and 1) and (LOGIN in ('.implode(',',$login).')) and (CLOSE_TIME between \''.$from.'\' and \''.$to.'\') and (right(SYMBOL,2) = \'bo\')';
            }else{
                $where='(CMD between 0 and 1) and (LOGIN in ('.implode(',',$login).')) and (CLOSE_TIME between \''.$from.'\' and \''.$to.'\') and (right(SYMBOL,2) <> \'bo\')';
            }
        }
        $list=$model->field('TICKET,LOGIN,OPEN_TIME,CMD,SYMBOL,VOLUME,OPEN_PRICE,SL,TP,CLOSE_TIME,CLOSE_PRICE,COMMISSION,SWAPS,PROFIT,COMMENT')
            ->where($where)->select();
        $data=[];//最终数据拼装
        foreach ($list as $k => $v){
            $data[]=array(
                'ticket'=>$v['ticket'],
                'login'=>$v['login'],
                'open_time'=>$v['open_time'],
                'open_price'=>$v['open_price'],
                'cmd'=>$v['cmd']==1 ? 'SELL' : 'BUY',
                'symbol'=>$v['symbol'],
                'volume'=>round($v['volume']/100,2),
                'sl'=>$v['sl'],
                'tp'=>$v['tp'],
                'close_time'=>$v['close_time'],
                'close_price'=>$v['close_price'],
                'commission'=>$v['commission'],
                'swaps'=>$v['swaps'],
                'profit'=>round($v['profit'],2),
                'comment'=>$v['comment']
            );
        }
        //print_r($data);die;
        $csv=new \Think\Csv();
        $csv_title=array('交易号','MT4','开单时间','开单价','交易类型','交易品种','交易量','SL','TP','关单时间','关单价','手续费','库存费','收益','注释');
        $csv->put_csv($data,$csv_title);
    }

    public function fundChange(){//出入金记录
        $this->display();
    }

    public function downloadPayments(){//下载出入金记录
        $from=I('post.from') ? I('post.from') : die('请选择开始时间，<a href="javascript:history.back(-1)">点击返回</a>');
        $to=I('post.to') ? I('post.to') : die('请选择结束时间，<a href="javascript:history.back(-1)">点击返回</a>');
        $type=I('post.type') ? I('post.type') : die('请选择按账号查询或组别查询，<a href="javascript:history.back(-1)">点击返回</a>');
        $comment=I('post.comment');
        $userModel=M('user');
        $use_where='(user_id>1000) ';
        if($type==1){
            $logins=I('post.keywords') ? I('post.keywords') : '%';
            if($logins!='%'){
                $use_where.=' and (user_id in ('.$logins.'))';
            }
        }else{
            $groups=I('post.keywords') ? I('post.keywords') : '%';
            if($groups!='%'){
                $groups=str_replace(",","','",$groups);//组别是字符串，所以需要加引号
                $use_where.=' and (`group` in (\''.$groups.'\'))';
            }
        }
        $users=$userModel->field('user_id')->where($use_where)->select();
        $login=[];
        foreach ($users as $ke => $va){
            $login[]=$va['user_id'];
        }
        //查询出入金历史
        $where='(CMD = 6) and (LOGIN in ('.implode(',',$login).')) and (CLOSE_TIME between \''.$from.'\' and \''.$to.'\') ';
        if($comment==1){//入金
            $where.=' and (COMMENT like \'%deposit%\') ';
        }else if($comment==2){//出金
            $where.=' and (COMMENT like \'%withdrawal%\') ';
        }else if($comment==3){//内转
            $where.=' and (COMMENT like \'%InTurn%\') ';
        }else if($comment==4){//返佣
            $where.=' and (COMMENT like \'%CommBo%\') ';
        }else if($comment==5){//外佣
            $where.=' and (COMMENT like \'%CommFDHs#FDHs%\') ';
        }else if($comment==6){//手续费
            $where.=' and (COMMENT like \'%COMMISSION%\') ';
        }
        $model=M('mt4_trades','','DB_CONFIG2');
        $reoups=$model->field('TICKET,LOGIN,CMD,CLOSE_TIME,PROFIT,COMMENT')->where($where)->order('TICKET desc')->select();
        $list=[];
        foreach ($reoups as $k => $v){
            $list[]=array(
                'ticket'=>$v['ticket'],
                'login'=>$v['login'],
                'cmd'=>$v['cmd']==6 ? 'Balance' : $v['cmd'],
                'close_time'=>$v['close_time'],
                'profit'=>round($v['profit'],2),
                'comment'=>$v['comment']
            );
        }
        $csv=new \Think\Csv();
        $csv_title=array('交易号','MT4','交易类型','时间','收益','注释');
        $csv->put_csv($list,$csv_title);
    }

    public function volumeSum(){//交易盈亏统计
        $this->display();
    }

    public function downloadVolumeSum(){//下载交易盈亏统计
        $change=I('post.change') ? I('post.change') : die('请选择查询时间段，<a href="javascript:history.back(-1)">点击返回</a>');
        $time=I('post.time') ? strtotime(I('post.time').':00:00') : strtotime(date('Y-m-d H').':00:00');
        $from=date('Y-m-d H:i:s',($time-3600+1));
        if($change==1){
            $from=date('Y-m-d H:i:s',($time-1800+1));
        }
        $to=date('Y-m-d H:i:s',$time);
        $type=I('post.type') ? I('post.type') : die('请选择按账号查询或组别查询，<a href="javascript:history.back(-1)">点击返回</a>');
        $transaction_type=I('post.transaction_type') ? I('post.transaction_type') : 0;
        $userModel=M('user');
        $use_where='(user_id>1000) ';
        if($type==1){
            $logins=I('post.keywords') ? I('post.keywords') : '%';
            if($logins!='%'){
                $use_where.=' and (user_id in ('.$logins.'))';
            }
        }else{
            $groups=I('post.keywords') ? I('post.keywords') : '%';
            if($groups!='%'){
                $groups=str_replace(",","','",$groups);//组别是字符串，所以需要加引号
                $use_where.=' and (`group` in (\''.$groups.'\'))';
            }
        }
        /*try{//放到自己数据库查询筛选一次，避免查询到其他白标账号
            $users=$userModel->field('user_id')->where($use_where)->select();
        }catch(\think\Exception $e) {
            //echo $e->getMessage();
            exit('您要查询的用户数量过多，请分批导入查询，<a href="javascript:history.back(-1)">点击返回</a>');
        }*/
        $users=$userModel->field('user_id,`group`')->where($use_where)->select();
        $login=[];
        $group=[];
        foreach ($users as $ke => $va){
            $login[]=$va['user_id'];
            $group[$va['user_id']]=$va['group'];
        }
        $section=C('section');//获取号段
        //查询交易
        $model=M('mt4_trades','','DB_CONFIG2');
        $where='(CMD between 0 and 1) and (LOGIN between '.$section['min'].' and '.$section['max'].') and (CLOSE_TIME between \''.$from.'\' and \''.$to.'\') and (LOGIN in ('.implode(',',$login).'))';
        if(empty($transaction_type)){//交易查询条件
            $where='(CMD between 0 and 1) and (LOGIN between '.$section['min'].' and '.$section['max'].') and (CLOSE_TIME between \''.$from.'\' and \''.$to.'\') and (LOGIN in ('.implode(',',$login).'))';
        }else{
            if($transaction_type==1){
                $where.=' and (right(SYMBOL,2) = \'bo\')';
            }else{
                $where.=' and (right(SYMBOL,2) <> \'bo\')';
            }
        }
        $list=$model->field('LOGIN,sum(VOLUME),sum(PROFIT)')->where($where)->group('LOGIN')->select();
        //print_r($list);die;
        $data=[];//最终数据拼装
        $timeDesc=date('Y-m-d H:i',($time-3600+1));
        if($change==1){
            $timeDesc=date('Y-m-d H:i:s',($time-1800+1));
        }
        foreach ($list as $k => $v){
            $data[]=array(
                'login'=>$v['login'],
                'group'=>$group[$v['login']],
                'volume'=>round($v['sum(volume)']/100,2),
                'profit'=>round($v['sum(profit)'],2),
                'time'=>$timeDesc.'--'.date('H:i',$time)
            );
        }
        //print_r($data);die;
        $csv=new \Think\Csv();
        $csv_title=array('交易号','组别','交易量','收益','时间');
        $csv->put_csv($data,$csv_title);
    }
}